package crm.control;


import java.sql.ResultSet;
import java.sql.SQLData;
import java.util.ArrayList;
import java.util.Collection;

import crm.dbcoon.DBConnect;
import crm.model.Statistics;
import crm.util.Pagination;

public class StatisticsControl {
	
	public static Pagination pageInstance = null;
	
	public static boolean addStatistics(Statistics s) {
		DBConnect dbConnect = new DBConnect();
		String sql= "insert into statistics(employee_id,recepit_id,add_money,date) values (?,?,?,?)";
		dbConnect.prepareStatement(sql);
		try {
			setRs(dbConnect, s);
			dbConnect.executeUpdate();
			return true;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("addError");
		}finally{
			dbConnect.close();
		}
		return false;
	}
	
	public static void setRs(DBConnect dbc,Statistics s) {
		int index=1;
		try{
			dbc.setInt(index++, s.getEmployee_id());
			dbc.setInt(index++, s.getRecepit_id());
			dbc.setString(index++, s.getAdd_money());
			dbc.setString(index++, s.getDate());
		}catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("setRsError");
		}
		
	}
	/**
	 * 获取回款列表
	 */
	public static Collection  getAllStaistics(String employeeName,String recepitTitle,String year,String month,int rowsPerPage,int pages) {
		String result = "0";
		String sql1 = "";
		String sql2="";
		String sql3="";
		int y1 = 0; int m1 = 0;
		int y2 = 0; int m2 = 0; 
		int d1 = 0; int d2 = 0;
		String employeeId = String.valueOf(EmployeesControl.getEmployeeIdByName(employeeName));
		//System.out.println("iiii"+employeeId+"dddd");
		String recepit_id = String.valueOf(RecepitControl.getRecepitIdByTitle(recepitTitle));
		if(employeeId.equals("0")||employeeId==null){
			sql1=" > 0";
		}else{
			sql1 = " ="+employeeId;
		}
		if(recepit_id==null||recepit_id.equals("0")){
			sql2 = " >0";
		}else{
			sql2 = " ="+recepit_id;
		}
		if(year==null||year.equals("")){
			sql3 = " and date like '%' ";
		}
		else{
			y1 = Integer.parseInt(year);
			y2 = y1+1;
			y1 = y1*10000;
			y2 = y2*10000;
			if(month==null||month.equals("0")){
				sql3 = " and date > '"+y1+"' and date < '"+y2+"'";
			}else{
				m1 = Integer.parseInt(month);
				m2 = m1+1;
				m1 = m1*100;
				m2 = m2*100;
				d1 = y1+m1;
				d2 = y1+m2;
				sql3 = " and date >'"+d1+"' and date < '"+d2+"' ";
			}
		}
		//String sqlString1 ="select sum(add_money) from statistics where employee_id "+sql1+" and recepit_id "+sql2+sql3;
		String sqlString2 ="select * from statistics where employee_id "+sql1+" and recepit_id "+sql2+sql3;
		pageInstance = new huikuanControlPage(sqlString2, rowsPerPage);
		System.out.println(sqlString2);
		DBConnect dbConnect = new DBConnect();
		dbConnect.prepareStatement(sqlString2);
		try {
			return pageInstance.getPage(pages);
//			ResultSet rs = dbConnect.executeQuery();
//			if(rs.next()){
//				if(rs.getString(1)!=null){
//				result= rs.getString(1);
//				return result;
//			}
//		}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("统计出错");
		}finally{
			dbConnect.close();
		}
		return null;
	}
	
	
	/**
	 *获取单个销售员的回款
	 */
	public static String  getStaistics(String employeeId,String recepit_id,String year,String month) {
		String result = "0";
		String sql1 = "";
		String sql2="";
		String sql3="";
		int y1 = 0; int m1 = 0;
		int y2 = 0; int m2 = 0; 
		int d1 = 0; int d2 = 0;
		if(employeeId.equals("")||employeeId==null){
			sql1=" > 0";
		}else{
			sql1 = " ="+employeeId;
		}
		if(recepit_id==null||recepit_id.equals("")){
			sql2 = " >0";
		}else{
			sql2 = " ="+recepit_id;
		}
		if(year==null||year.equals("")){
			sql3 = " and date like '%' ";
		}
		else{
			y1 = Integer.parseInt(year);
			y2 = y1+1;
			y1 = y1*10000;
			y2 = y2*10000;
			if(month==null||month.equals("0")){
				sql3 = " and date > '"+y1+"' and date < '"+y2+"'";
			}else{
				m1 = Integer.parseInt(month);
				m2 = m1+1;
				m1 = m1*100;
				m2 = m2*100;
				d1 = y1+m1;
				d2 = y1+m2;
				sql3 = " and date >'"+d1+"' and date < '"+d2+"' ";
			}
		}
		String sql ="select sum(add_money) from statistics where employee_id "+sql1+" and recepit_id "+sql2+sql3;
		System.out.println(sql);
		DBConnect dbConnect = new DBConnect();
		dbConnect.prepareStatement(sql);
		try {
			ResultSet rs = dbConnect.executeQuery();
			if(rs.next()){
				if(rs.getString(1)!=null){
				result= rs.getString(1);
				return result;
			}}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("统计出错");
		}finally{
			dbConnect.close();
		}
		return result;
	}
	
	/**
	 * 获取单个销售员的销售额(根据 )
	 */
	public static String getXiaoShouE(String employeeName,String year,String month) {
		String date =  "1900-01";
		String date2 = "5000-01";
		String clause =""; 
		String clause2="";
		if(year!=null&&!year.equals("")){
			if(month!=null&&!month.equals("0")){
				//date = year+"-"+month;
				if(Integer.parseInt(month)<10){
					date = year +"-"+"0"+(Integer.parseInt(month)) ;
				}else{
					date = year +"-"+(Integer.parseInt(month)) ;
				}
				if(Integer.parseInt(month)<9){
					date2 = year +"-"+"0"+(Integer.parseInt(month)+1) ;
				}else{
					date2 = year +"-"+(Integer.parseInt(month)+1) ;
				}
				clause = "> '"+date+"' and  control_recepit.creat_time < '"+date2+"'";
			}
			else{
				date = year+"-01";
				date2 = Integer.parseInt(year)+1+"-00";
				clause = "> '"+date+"' and  control_recepit.creat_time < '"+date2+"'";
				System.out.println(date2);
			}
		}else{
			clause = " like '%'";
		}
		if(employeeName==null||employeeName.equals("")){
			clause2 = " like '%' ";
		}else{
			clause2=" = '"+employeeName+"' ";
		}
		DBConnect dbConnect =new DBConnect();
		String sql = "select sum(deal_money) from control_bargain,control_recepit,control_offer where control_bargain.offer_id " +
				" = control_offer.id and control_offer.recepit_id = control_recepit.id and control_recepit.creator "+clause2+" and " +
						" control_recepit.creat_time "+clause;
		System.out.println(sql);
	    dbConnect.prepareStatement(sql);
	    try {
	    	ResultSet rs = dbConnect.executeQuery();
	    	if(rs.next()){
			    if(rs.getString(1)!=null){
			    	return rs.getString(1);
	    	     }
	    	}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("获取销售额失败 ");
		}finally{
			dbConnect.close();
		}
		return "0";
	}
	/**
	 * 获取rs
	 */
	public static void getRs(ResultSet rs,Statistics statistics) {
		try {
			statistics.setRecepit_id(rs.getInt("recepit_id"));
			statistics.setEmployee_id(rs.getInt("employee_id"));
			statistics.setDate(rs.getString("date"));
			statistics.setAdd_money(rs.getString("add_money"));
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("getRs方法出错");
		}
	}
	/**
	 * 根据employee_id查回款
	 */
	public static Collection searchHuiKuanByEmp(int employee_id,int rowsPerPage,int pages) {
		String sql = "select * from statistics where employee_id='"+employee_id+"'";
		System.out.println(sql);
		pageInstance = new huikuanControlPage(sql, rowsPerPage);
		try {
			return pageInstance.getPage(pages);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
	}
	
	/**
	 * 查询回款
	 */
	public static Collection searchHuikuan(int rowsPerPage,int pages) {
//		if(employee_id==null||employee_id.equals(""))
//			employee_id="%";
//		if(recepit_id==null||recepit_id.equals(""))
//			recepit_id="%";
//		if(add_money==null||add_money.equals(""))
//			add_money="%";
//		if(date==null||date.equals(""))
//			date="%";
		String sql="select statistics.* from statistics,info_employees,control_recepit where " +
				            "statistics.employee_id=info_employees.id and "+
		                      "statistics.recepit_id=control_recepit.id";
		System.out.println(sql);
		pageInstance = new huikuanControlPage(sql, rowsPerPage);
		try {
			return pageInstance.getPage(pages);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
		
	}

}
class huikuanControlPage extends Pagination{
	public huikuanControlPage (String sql,int rowsPerPage) {
		super(sql,rowsPerPage,1);
	}
	
	protected Collection<Statistics> packResultSet(ResultSet rs) {
		Collection<Statistics> sList = new ArrayList<Statistics>();
		try {
			   while(rs.next()){
			   Statistics st = new Statistics();
			   StatisticsControl.getRs(rs, st);
			   sList.add(st);
			   st=null;
			   }
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("Collection<Statistics>方法出错");
		}
		return sList;
	}
	

}
